package com.css.fxfzypg.modules.earthquakedisasterloss.repository.Impl;

import com.css.fxfzypg.modules.earthquakedisasterloss.entity.TrafficAnalysis;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgJcClimate;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgResultSlope;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.YpgResultSlopeData;
import com.css.fxfzypg.modules.earthquakedisasterloss.repository.CountLandslideRepository;
import com.css.fxfzypg.modules.hypergraph.service.HypergraphService;
import com.css.fxfzypg.modules.maps.service.impl.SlopeMapServiceImpl;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

/**
 * @Description: fxfzypg
 * @Author: lhl
 * @CreateDate: 2022/11/15 10:15
 */
@Repository
public class CountLandslideRepositoryImpl implements CountLandslideRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    SlopeMapServiceImpl slopeMapServiceImpl;
    @Resource
    HypergraphService hypergraphService;

    //tif文件路径
    @Value("${hypergraph.filePath}")
    String tifFilePath;

    @Override
    public Boolean getSTIntersects(String geomText, String geomText1) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))";

        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, geomText1);
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int saveYpgResultSlope(YpgResultSlope entity) {
        String sql = "insert into ypg_result_slope_new(uuid,dz_intensity,road_range,slope_version,lithology_version," +
                "phy_version,nat_den_version,result_source,file_path,task_id,extends1,extends2)" +
                "values(:uuid,:dzIntensity,:roadRange,:slopeVersion,:lithologyVersion," +
                ":phyVersion,:natDenVersion,:resultSource,:filePath,:taskId,:extends1,:extends2)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    //存储空间数据--四级、五级
    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public String saveYpgResultSlopeData(YpgResultSlope entity) {
        String filePath = tifFilePath + entity.getFilePath() + ".tif";
        String taskId = entity.getTaskId();
        String returnMsg = slopeMapServiceImpl.checkTifFilesSlopeRsult(filePath, taskId);
        if(returnMsg.indexOf("成功导入")!=-1){
            //导入数据
            String sql = "insert into ypg_result_slope(uuid,geom,result_level,task_id) " +
                    "select id,geom,value,fileuuid from ypg_result_slope_union where fileuuid='"+taskId+"'";
            jdbcTemplate.update(sql);
            //更新数据--一级
            String sql1 = "update ypg_result_slope set dz_intensity='"+entity.getDzIntensity()+
                    "',road_range="+entity.getRoadRange()+",result_level='一级',result_source='"+entity.getResultSource()+"',slope_number=1" +
                    " where task_id='"+taskId+"' and result_level='1'";
            jdbcTemplate.update(sql1);
            //更新数据--五级
            String sql2 = "update ypg_result_slope set dz_intensity='"+entity.getDzIntensity()+
                    "',road_range="+entity.getRoadRange()+",result_level='二级',result_source='"+entity.getResultSource()+"',slope_number=2" +
                    " where task_id='"+taskId+"' and result_level='2'";
            jdbcTemplate.update(sql2);
            //更新数据--三级
            String sql3 = "update ypg_result_slope set dz_intensity='"+entity.getDzIntensity()+
                    "',road_range="+entity.getRoadRange()+",result_level='三级',result_source='"+entity.getResultSource()+"',slope_number=3" +
                    " where task_id='"+taskId+"' and result_level='3'";
            jdbcTemplate.update(sql3);
            //更新数据--四级
            String sql4 = "update ypg_result_slope set dz_intensity='"+entity.getDzIntensity()+
                    "',road_range="+entity.getRoadRange()+",result_level='四级',result_source='"+entity.getResultSource()+"',slope_number=4" +
                    " where task_id='"+taskId+"' and result_level='4'";
            jdbcTemplate.update(sql4);
            //更新数据--五级
            String sql5 = "update ypg_result_slope set dz_intensity='"+entity.getDzIntensity()+
                    "',road_range="+entity.getRoadRange()+",result_level='五级',result_source='"+entity.getResultSource()+"',slope_number=5" +
                    " where task_id='"+taskId+"' and result_level='5'";
            jdbcTemplate.update(sql5);
            //删除数据
            String sql6 = "delete from ypg_result_slope_union where fileuuid='"+taskId+"'";
            jdbcTemplate.update(sql6);
        }

        return returnMsg;
    }

    @Override
    public List<YpgResultSlope> getByTaskId(String taskId) {
        String sql="select * from ypg_result_slope_new where task_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgResultSlope.class),taskId);
    }

    //根据任务ID、模型类型判断是否更换模型则删除全部计算结果
    @Override
    public boolean isDeleteAllResultByType(String taskId,String resultSource){
        //resultSource 1:逻辑斯蒂滑坡模型,2:黄土高原模型
        boolean bool = false;
        //查询地震滑坡计算结果列表
        String sql0 = "select * from ypg_result_slope_new where task_id = ?";
        List<YpgResultSlope> ypgResultSlopeList = jdbcTemplate.query(sql0, new BeanPropertyRowMapper<>(YpgResultSlope.class),taskId);
        if(ypgResultSlopeList!=null && ypgResultSlopeList.size()>0){
            for(YpgResultSlope ypgResultSlope : ypgResultSlopeList){
                if(!ypgResultSlope.getResultSource().equals(resultSource)){
                    bool = true;//存在不同模型的结果
                    break;
                }
            }
        }
        return bool;
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int delYpgResultSlope(String taskId,String dzIntensity) {
        delYpgResultSlopeData(taskId,dzIntensity);//删除空间数据
        String sql="delete from ypg_result_slope_new where task_id = '" + taskId + "'";
        if(dzIntensity!=null){
            sql += " and dz_intensity='" + dzIntensity + "'";
        }
        return jdbcTemplate.update(sql);
    }

    //删除空间数据
    public void delYpgResultSlopeData(String taskId,String dzIntensity) {
        /*slopeMapServiceImpl.deleteSlopeResultData(taskId);
        //删除空间数据
        String sql="delete from ypg_result_slope where task_id = ?";
        jdbcTemplate.update(sql, taskId);*/

        //查询地震滑坡计算结果列表
        String sql0 = "select * from ypg_result_slope_new where task_id = '" + taskId + "'";
        if(dzIntensity!=null){
            sql0 += " and dz_intensity='" + dzIntensity + "'";
        }
        List<YpgResultSlope> ypgResultSlopeList = jdbcTemplate.query(sql0, new BeanPropertyRowMapper<>(YpgResultSlope.class));
        if(ypgResultSlopeList!=null && ypgResultSlopeList.size()>0){
            for(YpgResultSlope ypgResultSlope : ypgResultSlopeList){
                String layerName = ypgResultSlope.getFilePath();
                String layerType = ypgResultSlope.getExtends1();//图层数据类型：最终计算、分级矢量化、缓冲区4级、缓冲区5级
                if(layerType.equals("最终计算")){
                    //根据名称、类型批量删除数据集
                    hypergraphService.batchDelByLayerType(layerName,"栅格");
                    layerName = layerName.replace("最终计算","分级计算");
                    //根据名称、类型批量删除数据集
                    hypergraphService.batchDelByLayerType(layerName,"栅格");
                }else{
                    //根据名称、类型批量删除数据集
//                    hypergraphService.batchDelByLayerType(layerName,"面");
                }
            }
            //刷新指定超图工作空间数据源数据集
            hypergraphService.getWorkspaceReload("ypgTifCount","");
        }
    }

    @Override
    public TrafficAnalysis getTrafficAnalysisByTaskId(String taskId,String dzIntensity) {
        String sql = "select * from ypg_result_traffic_analysis where task_id = ? and dz_intensity = ? and is_delete='0'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(TrafficAnalysis.class), taskId,dzIntensity);
        }catch (Exception e){

        }
        return null;
    }

    @Override
    public int saveTrafficAnalysis(TrafficAnalysis entity) {
        String sql = "insert into ypg_result_traffic_analysis(uuid,traffic_analysis,create_time,create_user_id" +
                ",create_user,is_delete,task_id,dz_intensity)" +
                "values(:uuid,:trafficAnalysis,:createTime,:createUserId,:createUser,:isDelete,:taskId,:dzIntensity)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int updateTrafficAnalysis(TrafficAnalysis entity) {
        String sql = "update ypg_result_traffic_analysis set " +
                "update_time=:updateTime,update_user=:updateUser,is_delete=:isDelete";
        if (PlatformObjectUtils.isNotEmpty(entity.getTrafficAnalysis())){
            sql=sql+",traffic_analysis=:trafficAnalysis";
        }
        sql=sql+" where task_id=:taskId and dz_intensity=:dzIntensity";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public String getSlopeModelIntensity(String taskId) {
        try {
            String sql="select distinct(dz_intensity) from ypg_result_slope_new where task_id = ?";
            String dzIntensity=jdbcTemplate.queryForObject(sql,String.class,taskId);
            return dzIntensity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int saveYpgJcClimate(YpgJcClimate entity) {
        String sql = "insert into ypg_jc_climate(id,av_prec,geom,task_id,values1)" +
                "values(:id,:avPrec,st_geometryfromtext(:geomText,4490),:taskId,:values1)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int delYpgJcClimate(String taskId) {
        String sql = "delete from ypg_jc_climate where task_id='" + taskId + "'";
        return jdbcTemplate.update(sql);
    }

}
